package Modelo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.jsp.jstl.sql.Result;
import javax.servlet.jsp.jstl.sql.ResultSupport;

public class AlumnoJDBC {

    private final String SQL_INSERT1 = "INSERT INTO alumnos "
            + "(carnet,nombre,apellidos,carrera,ciclo) VALUES (?,?,?,?,?);";
    private final String SQL_INSERT2 = "INSERT INTO reservaciones "
            + "(fecha,hora,id_alumno,id_computadora) VALUES (?,?,?,?);";
    private final String SQL_DELETE1 = "DELETE FROM alumnos "
            + "WHERE id_alumno = ?;";
    private final String SQL_DELETE2 = "DELETE FROM reservaciones "
            + "WHERE id_alumno= ?;";
    private final String SQL_SELECT_NUM_PC = "SELECT al.id_alumno,al.carnet,al.nombre,al.apellidos,al.carrera,"
            + "al.ciclo FROM alumnos AS al,reservaciones AS re WHERE re.id_computadora = ? "
            + "AND re.fecha = ? AND al.id_alumno=re.id_alumno;";
    private final String SQL_SELECT_FECHA = "SELECT al.id_alumno,al.carnet,al.nombre,"
            + "al.apellidos,al.carrera,al.ciclo FROM alumnos AS al,reservaciones AS re "
            + "WHERE re.fecha = ? AND al.id_alumno=re.id_alumno;";
    private final String SQL_SELECT_PCX = "SELECT co.id_computadora,co.tipo,co.marca "
            + "FROM computadoras AS co,reservaciones AS re WHERE re.id_alumno = ? "
            + "AND co.id_computadora=re.id_computadora;";
    private final String SQL_SELECT_ALUMX2 = "SELECT nombre "
            + "FROM alumnos WHERE id_alumno= ?;";
    private final String SQL_SELECT_ALUMX = "SELECT id_alumno FROM alumnos WHERE carnet= ? "
            + "AND nombre = ? AND apellidos = ? AND carrera = ? AND ciclo = ?;";
    private final String SQL_SELECT_ALLPC = "SELECT * FROM computadoras;";
    private final String SQL_SELECT_ALLALUM = "SELECT id_alumno,carnet,nombre,"
            + "apellidos,carrera,ciclo FROM alumnos";
    private final String SQL_SELECT_ALL = "SELECT * FROM reservaciones AS re "
            + "JOIN alumnos AS al ON al.id_alumno = re.id_alumno JOIN computadoras AS co "
            + "ON re.id_computadora = co.id_computadora;";
    //Variable para almacenar la conexion de referencia
    //Utilizada en uno de los constructores
    private Connection usrCon;

    public AlumnoJDBC() {
    }

    public AlumnoJDBC(Connection usrCon) {
        this.usrCon = usrCon;
    }

    public int insert2(String fecha, String hora, int id_alumno, int id_computadora) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        int nfilas = 0;

        try {
            conn = db.getConnection();
            stmt = conn.prepareStatement(SQL_INSERT2);

            //Asignando los parametros a la consulta SQL
            stmt.setString(1, fecha);
            stmt.setString(2, hora);
            stmt.setInt(3, id_alumno);
            stmt.setInt(4, id_computadora);
            nfilas = stmt.executeUpdate();
        } finally {
            db.close(stmt);
            db.close(conn);
        }
        return nfilas;
    }

    public int insert1(String carnet, String nombre, String apellidos, String carrera, int ciclo) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        int nfilas = 0;

        try {
            conn = db.getConnection();
            stmt = conn.prepareStatement(SQL_INSERT1);

            //Asignando los parametros a la consulta SQL
            stmt.setString(1, carnet);
            stmt.setString(2, nombre);
            stmt.setString(3, apellidos);
            stmt.setString(4, carrera);
            stmt.setInt(5, ciclo);
            nfilas = stmt.executeUpdate();
        } finally {
            db.close(stmt);
            db.close(conn);
        }
        return nfilas;
    }

    public int delete1(int id_alumno) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        int nfilas = 0;
        try {
            conn = db.getConnection();
            stmt = conn.prepareStatement(SQL_DELETE1);
            stmt.setInt(1, id_alumno);
            nfilas = stmt.executeUpdate();
        } finally {
            db.close(stmt);
            db.close(conn);

        }
        return nfilas;
    }

    public int delete2(int id_alumno) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        int nfilas = 0;
        try {
            conn = db.getConnection();
            stmt = conn.prepareStatement(SQL_DELETE2);
            stmt.setInt(1, id_alumno);
            nfilas = stmt.executeUpdate();
        } finally {
            db.close(stmt);
            db.close(conn);
        }
        return nfilas;
    }

    public List<Alumno> selectPcFecha(int id_computadora, String fecha) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Alumno alumno = null;
        List<Alumno> lstAlumnos = null;

        try {
            conn = db.getConnection();
            stmt = conn.prepareStatement(SQL_SELECT_NUM_PC);
            stmt.setInt(1, id_computadora);
            stmt.setString(2, fecha);
            rs = stmt.executeQuery();
            lstAlumnos = new ArrayList<Alumno>();
            while (rs.next()) {
                alumno = new Alumno();
                alumno.setId_alumno(rs.getInt(1));
                alumno.setCarnet(rs.getString(2));
                alumno.setNombre(rs.getString(3));
                alumno.setApellidos(rs.getString(4));
                alumno.setCarrera(rs.getString(5));
                alumno.setCiclo(rs.getInt(6));
                lstAlumnos.add(alumno);
            }
        } finally {
            db.close(rs);
            db.close(stmt);
            db.close(conn);
        }
        return lstAlumnos;
    }

    public List<Alumno> selectFecha(String fecha) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Alumno alumno = null;
        List<Alumno> lstAlumnos = null;

        try {
            conn = db.getConnection();
            stmt = conn.prepareStatement(SQL_SELECT_FECHA);
            stmt.setString(1, fecha);
            rs = stmt.executeQuery();
            lstAlumnos = new ArrayList<Alumno>();
            while (rs.next()) {
                alumno = new Alumno();
                alumno.setId_alumno(rs.getInt(1));
                alumno.setCarnet(rs.getString(2));
                alumno.setNombre(rs.getString(3));
                alumno.setApellidos(rs.getString(4));
                alumno.setCarrera(rs.getString(5));
                alumno.setCiclo(rs.getInt(6));
                lstAlumnos.add(alumno);
            }
        } finally {
            db.close(rs);
            db.close(stmt);
            db.close(conn);
        }
        return lstAlumnos;
    }

    public List<Alumno> listarAlumnos() throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Alumno> lstAlumnos = null;

        try {
            conn = db.getConnection();
            stmt = conn.prepareStatement(SQL_SELECT_ALLALUM);
            rs = stmt.executeQuery();
            lstAlumnos = new ArrayList<Alumno>();
            Alumno alumno = null;
            while (rs.next()) {
                alumno = new Alumno();
                alumno.setId_alumno(rs.getInt(1));
                alumno.setCarnet(rs.getString(2));
                alumno.setNombre(rs.getString(3));
                alumno.setApellidos(rs.getString(4));
                alumno.setCarrera(rs.getString(5));
                alumno.setCiclo(rs.getInt(6));
                lstAlumnos.add(alumno);
            }
        } finally {
            db.close(rs);
            db.close(stmt);
            db.close(conn);
        }
        return lstAlumnos;
    }

    public String[][] listarDatos() throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String Datos[][] = null;
        int numFilas;
        int i, j, k = 0;

        Result result = null;

        try {
            conn = db.getConnection();
            stmt = conn.prepareStatement(SQL_SELECT_ALL);
            rs = stmt.executeQuery();
            result = ResultSupport.toResult(rs);
            numFilas = result.getRowCount();
            Datos = new String[numFilas][14];
            rs.first();

            i = 0;

            if (rs.first()) {
                for (j = 0; j < 14; j++) {
                    Datos[i][j] = rs.getString(++k);
                }
                k = 0;
            }

//            rs.first();
            for (i = 1; i < numFilas - 1; i++) {
                if (rs.next()) {
                    System.out.println("i=" + i);
                    for (j = 0; j < 14; j++) {
                        Datos[i][j] = rs.getString(++k);
                        System.out.println("DATOS[" + i + "][" + j + "] = " + Datos[i][j]);
                    }
                    k = 0;
                } else {
                    break;
                }
            }

//            if (rs.last()) {
//                k = 0;
//                for (j = 0; j < 14; j++) {
//                    Datos[numFilas - 1][j] = rs.getString(++k);
//                }
//            }

        } finally {
            db.close(rs);
            db.close(stmt);
            db.close(conn);
        }
        return Datos;
    }

    public int contarFilas(String Datos[][]) {
        int i = 0, cont = 0;

        while (Datos[i][0] != null) {
            i++;
            cont++;
        }
        return cont;
    }

    public List<Computadora> listarComputadoras() throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Computadora> lstComputadoras = null;
        try {
            conn = db.getConnection();
            stmt = conn.prepareStatement(SQL_SELECT_ALLPC);
            rs = stmt.executeQuery();
            lstComputadoras = new ArrayList<Computadora>();
            Computadora pc = null;
            while (rs.next()) {
                pc = new Computadora();
                pc.setId_computadora(rs.getInt(1));
                pc.setTipo(rs.getString(2));
                pc.setMarca(rs.getString(3));
                lstComputadoras.add(pc);
            }
        } finally {
            db.close(rs);
            db.close(stmt);
            db.close(conn);
        }
        return lstComputadoras;
    }

    public Computadora selectComputadoraX(int id_alumno) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Computadora pc = null;

        try {
            conn = db.getConnection();
            stmt = conn.prepareStatement(SQL_SELECT_PCX);

            stmt.setInt(1, id_alumno);
            rs = stmt.executeQuery();

            if (rs.next()) {
                pc = new Computadora();
                pc.setId_computadora(rs.getInt(1));
                pc.setTipo(rs.getString(2));
                pc.setMarca(rs.getString(3));
            }
        } finally {
            db.close(rs);
            db.close(stmt);
            db.close(conn);
        }
        return pc;
    }

    public int selectAlumnoX(String carnet, String nombre, String apellidos, String carrera, int ciclo) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        int id_alumno = 0;

        try {
            conn = db.getConnection();
            stmt = conn.prepareStatement(SQL_SELECT_ALUMX);

            stmt.setString(1, carnet);
            stmt.setString(2, nombre);
            stmt.setString(3, apellidos);
            stmt.setString(4, carrera);
            stmt.setInt(5, ciclo);
            rs = stmt.executeQuery();

            if (rs.next()) {
                id_alumno = rs.getInt(1);
            }
        } finally {
            db.close(rs);
            db.close(stmt);
            db.close(conn);
        }
        return id_alumno;
    }

    public String selectAlumnoX2(int id_alumno) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String nombre = "";

        try {
            conn = db.getConnection();
            stmt = conn.prepareStatement(SQL_SELECT_ALUMX2);

            stmt.setInt(1, id_alumno);
            rs = stmt.executeQuery();

            if (rs.next()) {
                nombre = rs.getString(1);
            }
        } finally {
            db.close(rs);
            db.close(stmt);
            db.close(conn);
        }
        return nombre;
    }
}
